﻿use master
go

create database Students
on
(
	name=Students,
	filename='D:\zuoye\Studens.mdf',
	size=10MB,
	maxsize=50MB,
	filegrowth=10%
)
log on
(
	name=Students_log,
	filename='D:\zuoye\Studens_log.ldf',
	size=10MB,
	maxsize=50MB,
	filegrowth=10%
)
go

use Students
go

create table ClassInfo
 (
	ClassID int primary Key identity(1,1),
	ClassName nvarchar(20) unique not null,
 )  

 insert into ClassInfo (ClassName) values ('软件10班') ,('软件2班') ,('软件3班') ,('软件4班') ,
('软件5班') ,('软件6班') ,('软件7班') ,('软件8班') ,('软件9班') 
select * from ClassInfo
update ClassInfo set ClassName='软件11班' where ClassID=1
delete from ClassInfo where ClassID=10

create table Student
(
	StuID int primary Key identity(1,1),
	ClassID int references ClassInfo(ClassID),
	StuName nvarchar(20) not null,
	StuSex nchar(1) default('男') check(StuSex='男'or StuSex='女'),--StuSex in （'男','女'）
	StuBrithday date,
	StuPhone nvarchar(11) unique not null,
	StuAddress nvarchar(200),
	CreateDate datetime default(getdate())
)
select * from Student
insert into Student values(null,'张三','男',null,1,null,default),(null,'张三1','男',null,2,null,default),
(null,'张三2','男',null,3,null,default),(null,'张三7','男',null,12,null,default),(null,'张三12','男',null,12345456,null,default),
(null,'张三3','男',null,4,null,default),(null,'张三8','男',null,11,null,default),(null,'张三13','男',null,12354456,null,default),
(null,'张三4','男',null,5,null,default),(null,'张三9','男',null,40,null,default),(null,'张三14','男',null,123483756,null,default),
(null,'张三5','男',null,6,null,default),(null,'张三10','男',null,9,null,default),(null,'张三15','男',null,45,null,default),
(null,'张三6','男',null,7,null,default),(null,'张三11','男',null,8,null,default),(null,'张三16','男',null,13,null,default)
delete from Student where ClassID=1


create table Course
(
	CourseID int primary Key identity(1,1),
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) not null check(CourseCredit>=1 and CourseCredit<=5),
	Coursetype nvarchar(10) check(Coursetype='专业课' or Coursetype='公开课')
)
select * from Course
insert into Course(CourseName) values('语文'),('数学'),('英语'),('计算机'),('德育'),('职业生涯'),('生活')
update Course set CourseCredit=5 where CourseName='计算机'

use Students
go
create table Score
(
	ScoreID int primary Key identity(1,1),
	StuID int references Student(StuID),
	CourseID int references Course(CourseID),
	Score decimal(5,2) default(0) unique not null
)
select * from Score
insert into Score values(1,1,8),(2,2,14),(3,3,10),(4,4,11),(5,5,12),(6,6,9),(7,7,13)
delete Score where StuID=1
delete Score where ScoreID=18
alter table Score add constraint CK_Score_Score check(Score>=0 and Score<=100)